# -*- coding:utf-8 -*-
# @Author   : ZGS
# @Time     : 2021/1/4 18:24
# @FileName : pymysql_.py
# 我拿青春赌明天!
import pymysql
import pandas as pd


class PyMysql(object):
    host = "127.0.0.1"
    user = "root"
    passwd = "123456"
    db = "epi2020"

    def connect_cursor(self):
        con = pymysql.connect(self.host, self.user, self.passwd, self.db)
        return con, con.cursor()

    def verify(self):
        con, cursor = self.connect_cursor()
        print(cursor.execute("select version()").fetchone())
        con.close()

    def look_up(self, table, column_list):
        """
        查询数据是否存在
        :param table:
        :param column_list:
        :return:
        """
        con, cursor = self.connect_cursor()
        print(f"查询 {column_list[0]}: {column_list[1]}")
        res = cursor.execute(f"select * from {table} where {column_list[0]}='{column_list[1]}'")
        print(f"查询结果: {res}")
        return res

    def query_data(self, table='area_show', filter_=None):
        con, cursor = self.connect_cursor()
        if isinstance(filter_, tuple):
            res = cursor.execute(f"select * from {table} where {filter_[0]}='{filter_[1]}'")
            res_data = res.fetchall()
            return res_data
        else:
            res = cursor.execute(f"select * from {table}")
            res_data = res.fetchall()
            return res_data

    def insert_table(self, table, dic):
        con, cursor = self.connect_cursor()
        if isinstance(dic, dict):
            key = [keys for keys, value in dic.items()]
            value = [value for keys, value in dic.items()]
            key_str = f"{tuple(key)}".replace("'", "`")

            insert_sql = f"insert into  {self.db}.{table}{key_str}values{tuple(value)};"
            print(insert_sql)
            res_info = cursor.execute(insert_sql)
            con.commit()
            return res_info

    def to_pandas(self, table):
        con, cursor = self.connect_cursor()
        try:
            select_sql = f"select * from {self.db}.{table}"
            res = cursor.execute(select_sql)
            data = cursor.fetchall()
            columns = cursor.description

            df = pd.DataFrame(columns=[columns[index][0] for index in range(len(columns))],  data=list(data))

        except Exception as e:
            print(f"error: {e}")
            df = pd.DataFrame()
        return df

    def close_mysql(self):

        con, cursor = self.connect_cursor()
        cursor.close()
        con.close()


if __name__ == '__main__':
    epi = PyMysql()
    # epi.look_up(table='area_show', column_list=['area_name', '河南'])
    df = epi.to_pandas(table='area_show')
    # 将数据每一行转换成字典
    print(df.to_dict(orient='records'))
